﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Windows;
using StudentManagement.Entities;

namespace StudentManagement
{
    /// <summary>
    /// Interaction logic for FrmStudent.xaml
    /// </summary>
    public partial class FrmStudent
    {
        private readonly bool _isUpdate;

        public FrmStudent()
        {
            InitializeComponent();
            LoadSubjects();
            _isUpdate = false;
        }

        public FrmStudent(Student student)
        {
            InitializeComponent();
            PopulateData(student);
            txtCode.IsEnabled = false;
            _isUpdate = true;
        }

        private void PopulateData(Student student)
        {
            try
            {
                // Populate TextBoxes
                txtCode.Text = student.Student_Code;
                txtName.Text = student.Student_Name;
                txtClassCode.Text = student.Class_Code;
                txtCurrentAddress.Text = student.Current_Address;
                txtContactAddress.Text = student.Contact_Address;

                // DOB and Sex
                dtpDOB.SelectedDate = student.Student_DOB;
                if (student.Student_Sex)
                    radMale.IsChecked = true;
                else 
                    radFemale.IsChecked = true;

                // Marks
                LoadStudentMarks(student.Student_Code);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void btnClose_Click(object sender, RoutedEventArgs e)
        {
            Close();
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (_isUpdate)
                    UpdateStudent();
                else
                    AddStudent();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void AddStudent()
        {
            OleDbConnection conn = null;
            try
            {
                if (!ValidateData()) return;

                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                // Build query
                String strCommand =
                    "INSERT INTO StudentTable (Student_Code, Student_Name, Class_Code, Student_DOB, Student_Sex, Current_Address, Contact_Address)" +
                    " VALUES( "
                    + "'" + txtCode.Text.Trim() + "', "
                    + "'" + txtName.Text.Trim() + "', "
                    + "'" + txtClassCode.Text.Trim() + "', "
                    + "'" + dtpDOB.SelectedDate + "', "
                    + radMale.IsChecked + ", "
                    + "'" + txtCurrentAddress.Text + "', "
                    + "'" + txtContactAddress.Text + "' );";

                var cmd = new OleDbCommand(strCommand, conn);
                cmd.ExecuteNonQuery();

                // Add records to Marks table
                foreach (var studentMark in dgMarks.ItemsSource)
                {
                    String strCmd =
                    "INSERT INTO Student_Subject_ExamTable (Student_Code, Subject_Code, Exam_Seat_Time, Mark, Com_Exam)" +
                    " VALUES( "
                    + "'" + txtCode.Text.Trim() + "', "
                    + "'" + ((StudentMarks)studentMark).Subject_Code + "', "
                    + ((StudentMarks)studentMark).Exam_Seat_Time + ", "
                    + ((StudentMarks)studentMark).Mark + ", "
                    + "'" + ((StudentMarks)studentMark).Com_Exam + "' );";

                    var cmdMark = new OleDbCommand(strCmd, conn);
                    cmdMark.ExecuteNonQuery();
                }

                MessageBox.Show("Add Student successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);
                Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }

        private bool ValidateData()
        {
            if (string.IsNullOrEmpty(txtCode.Text.Trim()))
            {
                MessageBox.Show("Student Code can not be empty.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                txtCode.Focus();
                return false;
            }
            var selectedDateTime = dtpDOB.SelectedDate;
            if (selectedDateTime == null)
            {
                MessageBox.Show("Please select a date of birth.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                dtpDOB.Focus();
                return false;
            }
            if (DateTime.Now.Year - ((DateTime)selectedDateTime).Year < 18)
            {
                MessageBox.Show("Student can not less than 18 years old.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                dtpDOB.Focus();
                return false;
            }

            //foreach (var item in dgMarks.ItemsSource)
            //{
            //    if (((StudentMarks)item).Exam_Seat_Time)
            //    ((StudentMarks)item).Mark
            //}

            return true;
        }

        private void UpdateStudent()
        {
            OleDbConnection conn = null;
            try
            {
                if (!ValidateData()) return;

                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                String strCommand =
                    "UPDATE StudentTable SET "
                    + "Student_Code = '" + txtCode.Text
                    + "', Student_Name = '" + txtName.Text.Trim()
                    + "', Class_Code = '" + txtClassCode.Text.Trim()
                    + "', Student_DOB = '" + dtpDOB.SelectedDate
                    + "', Student_Sex = " + radMale.IsChecked
                    + ", Current_Address = '" + txtCurrentAddress.Text.Trim()
                    + "', Contact_Address = '" + txtContactAddress.Text.Trim()
                    + "' WHERE Student_Code = '" + txtCode.Text + "'";

                var cmd = new OleDbCommand(strCommand, conn);
                cmd.ExecuteNonQuery();

                // Update Marks
                foreach (var studentMark in dgMarks.ItemsSource)
                {
                    String strCmd =
                        "UPDATE Student_Subject_ExamTable SET "
                        + "Student_Code = '" + txtCode.Text.Trim()
                        + "', Subject_Code = '" + ((StudentMarks) studentMark).Subject_Code
                        + "', Exam_Seat_Time = " + ((StudentMarks) studentMark).Exam_Seat_Time
                        + ", Mark = " + ((StudentMarks) studentMark).Mark
                        + ", Com_Exam = '" + ((StudentMarks) studentMark).Com_Exam
                        + "' WHERE Student_Code = '" + txtCode.Text.Trim()
                        + "' AND Subject_Code = '" + ((StudentMarks)studentMark).Subject_Code + "';";

                    var cmdMark = new OleDbCommand(strCmd, conn);
                    cmdMark.ExecuteNonQuery();
                }

                MessageBox.Show("Update Subject successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);
                Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }

        private void LoadStudentMarks(string studentCode)
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                var strCmd = "SELECT Student_Subject_ExamTable.Subject_Code, SubjectTable.Subject_Name, SubjectTable.Term, SubjectTable.Subject_Abbreviation, SubjectTable.No_Of_Credit, Student_Subject_ExamTable.Exam_Seat_Time, Student_Subject_ExamTable.Mark, Student_Subject_ExamTable.Com_Exam"
                             + " FROM SubjectTable INNER JOIN Student_Subject_ExamTable ON SubjectTable.Subject_Code = Student_Subject_ExamTable.Subject_Code"
                             + " WHERE (((Student_Subject_ExamTable.Student_Code)='" + studentCode + "'));";

                var cmd = new OleDbCommand(strCmd, conn);
                reader = cmd.ExecuteReader();

                var lstMarks = new List<StudentMarks>();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var std = new StudentMarks();

                        std.Subject_Code = reader.GetString(0);
                        std.Subject_Name = reader.GetString(1);
                        std.Term = reader.GetString(2);
                        std.Subject_Abbreviation = reader.GetString(3);
                        std.No_Of_Credit = string.IsNullOrEmpty(reader.GetValue(4).ToString()) ? 0 : Convert.ToInt32(reader.GetValue(4));
                        std.Exam_Seat_Time = string.IsNullOrEmpty(reader.GetValue(5).ToString()) ? 0 : Convert.ToInt32(reader.GetValue(5));
                        std.Mark = string.IsNullOrEmpty(reader.GetValue(6).ToString()) ? 0 : Convert.ToInt32(reader.GetValue(6));
                        std.Com_Exam = reader.GetString(7);

                        lstMarks.Add(std);
                    }
                }
                dgMarks.ItemsSource = null;
                dgMarks.ItemsSource = lstMarks;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }

        private void LoadSubjects()
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                const string strCmd = "SELECT * FROM SubjectTable;";

                var cmd = new OleDbCommand(strCmd, conn);
                reader = cmd.ExecuteReader();

                var lstMarks = new List<StudentMarks>();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var std = new StudentMarks();

                        std.Subject_Code = reader.GetString(0);
                        std.Subject_Name = reader.GetString(1);
                        std.Term = reader.GetString(2);
                        std.Subject_Abbreviation = reader.GetString(3);
                        std.No_Of_Credit = string.IsNullOrEmpty(reader.GetValue(4).ToString()) ? 0 : Convert.ToInt32(reader.GetValue(4));
                        std.Exam_Seat_Time = 0;
                        std.Mark = 0;
                        std.Com_Exam = string.Empty;

                        lstMarks.Add(std);
                    }
                }
                dgMarks.ItemsSource = null;
                dgMarks.ItemsSource = lstMarks;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }
    }
}
